USE SAS
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SearchContacts')
	BEGIN
		DROP  Procedure  dbo.SearchContacts
	END

GO

CREATE Procedure dbo.SearchContacts
	@TEXT			VARCHAR(MAX)
,	@userId			VARCHAR(10)
AS
BEGIN
	
	DECLARE 
		@P_CATEGORY INT
	,	@P_TYPE		INT
	,	@R_OWNER	INT

	SELECT
		@P_CATEGORY = 1
	,	@P_TYPE		= 3
	,	@R_OWNER	= 3

	;WITH A AS(
		SELECT
			C.PartnerNo				as [ContactNo]
		,	C.PartnerLastName		as [LastName]
		,	C.PartnerFirstName		as [FirstName]
		,	C.PartnerGUID			as [PKID]
		FROM TR_PARTNER C WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS AS R WITH (NOLOCK)
			ON R.SourceGUID = C.PartnerGUID
		WHERE
			C.PartnerCategory	= @P_CATEGORY
		AND C.PartnerType		= @P_TYPE		
		AND	R.RelationShipType	= @R_OWNER
		AND R.Partner2 = @userID
		AND	(C.PartnerFirstName LIKE @text 
			OR C.PartnerLastName LIKE @text)

		UNION
		-- SEARCH IN NOTES
		SELECT
			C.PartnerNo				as [ContactNo]
		,	C.PartnerLastName		as [LastName]
		,	C.PartnerFirstName		as [FirstName]
		,	C.PartnerGUID			as [PKID]
		FROM TR_PARTNER C WITH (NOLOCK)
		INNER JOIN TR_NOTE AS N WITH (NOLOCK)
			ON N.SourceGUID = C.PartnerGUID
		INNER JOIN TR_RELATIONSHIPS AS R WITH (NOLOCK)
			ON R.SourceGUID = C.PartnerGUID
		WHERE
			C.PartnerCategory	= @P_CATEGORY	-- ORGANIZATION
		AND C.PartnerType		= @P_TYPE		-- CHILD A/C
		AND	R.RelationShipType	= @R_OWNER
		AND R.Partner2 = @userID
		AND	N.NoteText LIKE @text

		UNION
		-- SEARCH IN ADDRESS
		SELECT
			C.PartnerNo				as [ContactNo]
		,	C.PartnerLastName		as [LastName]
		,	C.PartnerFirstName		as [FirstName]
		,	C.PartnerGUID			as [PKID]
		FROM TR_PARTNER C WITH (NOLOCK)
		INNER JOIN TR_ADDRESS AS Ad WITH (NOLOCK)
			ON Ad.PartnerGUID = C.PartnerGUID
		INNER JOIN TR_RELATIONSHIPS AS R WITH (NOLOCK)
			ON R.SourceGUID = C.PartnerGUID
		INNER JOIN TR_STATES AS S WITH (NOLOCK)
			ON S.State = Ad.State
		WHERE
			C.PartnerCategory	= @P_CATEGORY	-- ORGANIZATION
		AND C.PartnerType		= @P_TYPE		-- CHILD A/C
		AND	R.RelationShipType	= @R_OWNER
		AND R.Partner2 = @userID
		AND	(Ad.Street1 LIKE @text 
			OR Ad.Street2 LIKE @text 
			OR Ad.City LIKE @text
			OR Ad.State LIKE @text
			OR S.StateDesc LIKE @text)
	), B AS (
		SELECT
			p.PartnerName			as [AccountName]
		,	A.PKID
		FROM TR_PARTNER As p WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.Partner2 = p.PartnerNo
		INNER JOIN A
			ON A.PKID = r.SourceGUID
		WHERE
			r.RelationshipType = 8
		AND	p.PartnerCategory = 2 -- ORGANIZATION
		AND p.PartnerType = 2 -- CHILD ACCOUNT
	), C AS (
		-- CONTACT TABLE
		SELECT
			con.JobTitle
		,	A.PKID
		FROM TR_CONTACT as con WITH (NOLOCK)
		INNER JOIN A
			ON A.PKID = con.ContactGUID
	), D AS (
		-- COMMUNICATION
		SELECT
			com.Phone 
		,	com.CellPhone
		,	A.PKID
		FROM TR_COMMUNICATION as com WITH (NOLOCK)
		INNER JOIN A
			ON A.PKID = com.PartnerGUID
	), FINAL AS (
		SELECT 
			A.ContactNo				AS [Contact No]
		,	A.LastName				As [Last Name]
		,	A.FirstName				AS [First Name]
		,	B.AccountName			as [Account Name]
		,	C.JobTitle				AS [Job Title]
		,	D.Phone					AS [Business Phone]
		,	D.CellPhone				As [Mobile Phone]
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			ON B.PKID = A.PKID
		LEFT OUTER JOIN C
			ON C.PKID = A.PKID
		LEFT OUTER JOIN D
			ON D.PKID = A.PKID
	)


	SELECT * FROM FINAL ORDER BY [Last Name], [First Name]
END
GO


-- SearchContacts @USERID = NULL